package com.lang.generator.core.helper;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLPrimaryKey;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlCreateTableParser;
import com.lang.generator.core.sql.dialect.MySQLDialect;
import com.lang.generator.domain.entity.TableField;
import com.lang.generator.domain.entity.TableStructure;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * sql解析器辅助
 *
 * @author Lang
 * @date 2022/11/19
 */
@Slf4j
@Component
public class SqlParserHelper {

    private Map<String, String> typeMap;

    private static final MySQLDialect sqlDialect = new MySQLDialect();

    @PostConstruct
    public void init() {
        this.typeMap = MapUtil.builder("tinyint", "Integer")
                .put("smallint", "Integer")
                .put("mediumint", "Integer")
                .put("int", "Integer")
                .put("integer", "Integer")
                .put("bigint", "Long")
                .put("double", "Double")
                .put("float", "Float")
                .put("decimal", "BigDecimal")
                .put("char", "String")
                .put("varchar", "String")
                .put("date", "Date")
                .put("time", "Date")
                .put("year", "Date")
                .put("datetime", "Date")
                .put("timestamp", "Long")
                .put("longtext", "String")
                .put("text", "String")
                .put("mediumtext", "String")
                .put("tinytext", "String").build();
    }


    public void formatTableStructure(TableStructure tableStructure) {
        String tableName = tableStructure.getTableName();
        if (StrUtil.isNotBlank(tableStructure.getPrefix())) {
            tableName = tableName.replace(tableStructure.getPrefix(), "");
        }
        String classNameUppercase = convertToCamelCase(tableName);
        tableStructure.setClassNameUppercase(classNameUppercase);
        tableStructure.setClassName(lowerFirst(classNameUppercase));
        tableStructure.getTableFields().forEach(x -> {
            String attrNameUppercase = convertToCamelCase(x.getFieldName());
            x.setAttrNameUppercase(attrNameUppercase);
            x.setAttrName(lowerFirst(attrNameUppercase));
            x.setAttrType(getAttrType(x.getFieldType()));
        });
    }

    private String getAttrType(String fieldType) {
        fieldType = fieldType.trim();
        fieldType =  ReUtil.delAll("\\(.*\\)", fieldType);
        return typeMap.get(fieldType);
    }

    private String lowerFirst(String str) {
        char[] cs = str.toCharArray();
        cs[0] += 32;
        return String.valueOf(cs);
    }

    private String convertToCamelCase(String name) {
        StringBuilder result = new StringBuilder();
        // 快速检查
        if (name == null || name.isEmpty()) {
            // 没必要转换
            return "";
        } else if (!name.contains("_")) {
            // 不含下划线，仅将首字母大写
            return name.substring(0, 1).toUpperCase() + name.substring(1);
        }
        // 用下划线将原始字符串分割
        String[] camels = name.split("_");
        for (String camel : camels) {
            // 跳过原始字符串中开头、结尾的下换线或双重下划线
            if (camel.isEmpty()) {
                continue;
            }
            // 首字母大写
            result.append(camel.substring(0, 1).toUpperCase());
            result.append(camel.substring(1).toLowerCase());
        }
        return result.toString();
    }


    public List<String> splitSql(String sql) {
        if (StrUtil.isBlank(sql)) {
            return null;
        }
        sql = sql.trim();
        String createTableRe = "(?i)create\\s*table";
        String create = "create table";
        sql = ReUtil.replaceAll(sql, createTableRe, create);
        return StrUtil.split(sql, create, true, true).stream().map(e -> create + " " + e).collect(Collectors.toList());
    }

    public List<TableStructure> buildFromSqlList(String sql) {
        List<String> sqlList = splitSql(sql);
        return sqlList.stream().map(this::buildFromSql).filter(e -> ObjectUtil.isNotNull(e) && CollectionUtil.isNotEmpty(e.getTableFields())).collect(Collectors.toList());
    }

    /**
     * 根据德鲁伊解析sql
     *
     * @param sql 建表 SQL
     * @return 生成的 TableSchema
     */
    public TableStructure buildFromSql(String sql) {
        if (StrUtil.isBlank(sql)) {
            return null;
        }
        try {
            // 解析 SQL
            MySqlCreateTableParser parser = new MySqlCreateTableParser(sql);
            SQLCreateTableStatement sqlCreateTableStatement = parser.parseCreateTable();
            TableStructure tableSchema = new TableStructure();
            tableSchema.setDbName(sqlCreateTableStatement.getSchema());
            tableSchema.setTableName(sqlDialect.parseTableName(sqlCreateTableStatement.getTableName()));
            String tableComment = null;
            if (sqlCreateTableStatement.getComment() != null) {
                tableComment = sqlCreateTableStatement.getComment().toString();
                if (tableComment.length() > 2) {
                    tableComment = tableComment.substring(1, tableComment.length() - 1);
                }
            }
            tableSchema.setTableComment(tableComment);
            List<TableField> fieldList = new ArrayList<>();
            // 解析列
            for (SQLTableElement sqlTableElement : sqlCreateTableStatement.getTableElementList()) {
                // 主键约束
                if (sqlTableElement instanceof SQLPrimaryKey) {
                    SQLPrimaryKey sqlPrimaryKey = (SQLPrimaryKey) sqlTableElement;
                    String primaryFieldName = sqlDialect.parseFieldName(sqlPrimaryKey.getColumns().get(0).toString());
                    fieldList.forEach(field -> {
                        if (field.getFieldName().equals(primaryFieldName)) {
                            field.setPrimaryKey(true);
                        }
                    });
                } else if (sqlTableElement instanceof SQLColumnDefinition) {
                    // 列
                    SQLColumnDefinition columnDefinition = (SQLColumnDefinition) sqlTableElement;
                    TableField field = new TableField();
                    field.setFieldName(sqlDialect.parseFieldName(columnDefinition.getNameAsString()));
                    field.setFieldType(columnDefinition.getDataType().toString());
                    String defaultValue = null;
                    if (columnDefinition.getDefaultExpr() != null) {
                        defaultValue = columnDefinition.getDefaultExpr().toString();
                    }
                    field.setDefaultValue(defaultValue);
                    field.setNotNull(columnDefinition.containsNotNullConstaint());
                    String comment = null;
                    if (columnDefinition.getComment() != null) {
                        comment = columnDefinition.getComment().toString();
                        if (comment.length() > 2) {
                            comment = comment.substring(1, comment.length() - 1);
                        }
                    }
                    field.setFieldComment(comment);
                    field.setPrimaryKey(columnDefinition.isPrimaryKey());
                    field.setAutoIncrement(columnDefinition.isAutoIncrement());
                    String onUpdate = null;
                    if (columnDefinition.getOnUpdate() != null) {
                        onUpdate = columnDefinition.getOnUpdate().toString();
                    }
                    field.setOnUpdate(onUpdate);
                    fieldList.add(field);
                }
            }
            tableSchema.setTableFields(fieldList);
            return tableSchema;
        } catch (Exception e) {
            log.error("SQL 解析错误", e);
            return null;
        }
    }


    /**
     * 正则方式解析sql
     *
     * @param sql
     * @return
     */
    public List<TableStructure> parserCreateSql(String sql) {
        sql = sql.replace("`", "");
        String createTableRe = "create\\s*table";
        int count = ReUtil.count(Pattern.compile(createTableRe, Pattern.CASE_INSENSITIVE), sql);
        //忽略大小写
        StringBuilder tablesRe = new StringBuilder("(?i)");
        for (int i = 0; i < count; i++) {
            tablesRe.append(createTableRe).append("(.*)");
        }
        List<String> allGroups = ReUtil.getAllGroups(Pattern.compile(tablesRe.toString(), Pattern.DOTALL), sql, false, true);

        return allGroups.stream().map(tableStr -> {
            // 贪婪获取括号中的所有字段
            String fieldsRe = "\\((.*)\\)";
            String fields = ReUtil.getGroup1(Pattern.compile(fieldsRe, Pattern.DOTALL), tableStr);
            // 移除最外层括号中的所有内容（字段） 方便获取表名
            String tempTableStr = tableStr.replace(fields, "");
            String tableCommentRe = "(?i)\\s*(.*)\\s*\\(.*\\).*COMMENT\\s*=\\s*'(.*)'";
            String tableRe = "(?i)\\s*(.*)\\s*\\(.*\\)";
            String tableName = ReUtil.getGroup1(Pattern.compile(tableRe, Pattern.DOTALL), tempTableStr);
            if (StrUtil.isNotBlank(tableName)) {
                tableName = tableName.trim();
            }
            String tableComment = ReUtil.get(Pattern.compile(tableCommentRe, Pattern.DOTALL), tempTableStr, 2);
            //找出字段描述中保护逗号(,)的 替换
            String replaceRe = "(?i)COMMENT\\s*'(.*,.*)'";
            List<String> conflictFileCommentList = ReUtil.findAllGroup1(Pattern.compile(replaceRe, Pattern.CANON_EQ), fields);
            for (String fileComment : conflictFileCommentList) {
                fields = fields.replace(fileComment, fileComment.replace(",", ""));
            }
            String primaryKeyRe = "(?i)PRIMARY\\s*KEY\\s*\\((.*)\\)";
            List<String> primaryKeys = ReUtil.findAllGroup1(Pattern.compile(primaryKeyRe, Pattern.CANON_EQ), fields);
            for (String primaryKey : primaryKeys) {
                fields = fields.replace(primaryKey, primaryKey.replace(",", "|"));
            }
            String replaceRe3 = "(?i)INDEX\\s*(.*,.*)";
            List<String> conflictFileCommentList3 = ReUtil.findAllGroup1(Pattern.compile(replaceRe3, Pattern.CANON_EQ), fields);
            for (String fileComment : conflictFileCommentList3) {
                fields = fields.replace(fileComment, fileComment.replace(",", "|"));
            }
            //去除干扰逗号后 按都逗号分组
            String[] fieldArr = fields.split(",");
            List<TableField> tableFields = Arrays.stream(fieldArr).map(field -> {
                List<String> list = Arrays.stream(field.split(" ")).filter(StrUtil::isNotBlank).collect(Collectors.toList());
                String fieldName = "", fieldType = "", fieldComment = "";
                if (!StrUtil.equalsIgnoreCase(list.get(0), "PRIMARY") && !StrUtil.equalsIgnoreCase(list.get(0), "INDEX")) {
                    fieldName = list.get(0).trim();
                    fieldType = list.get(1).replace("(.*)", "").toLowerCase();
                    String fieldCommentRe = "(?i)COMMENT\\s*'(.*)'";
                    fieldComment = ReUtil.getGroup1(fieldCommentRe, field);
                }
                TableField tableField = new TableField();
                tableField.setFieldName(fieldName);
                tableField.setFieldType(fieldType);
                tableField.setFieldComment(fieldComment);
                return tableField;
            }).filter(e -> StrUtil.isNotBlank(e.getFieldName()) || StrUtil.isNotBlank(e.getFieldType())).collect(Collectors.toList());
            //设置主键
            if (ObjectUtil.isNotNull(primaryKeys.get(0))) {
                String[] primaryKeyArr = primaryKeys.get(0).split(",");
                tableFields.forEach(e -> Arrays.stream(primaryKeyArr).forEach(x -> e.setPrimaryKey(StrUtil.equals(e.getFieldName(), x))));
            }
            TableStructure tableStructure = new TableStructure();
            tableStructure.setTableName(tableName);
            tableStructure.setTableComment(tableComment);
            tableStructure.setTableFields(tableFields);
            return tableStructure;
        }).collect(Collectors.toList());
    }

}
